﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Shl01.Models;
using Shl01.Dal;
using System.Data;
using System.IO;

namespace Shl01.Dal
{
    public class Sql
    {
        public void SetData(string DatabaseName,Conn conn, string Path)
        {
            try
            {
                string database = SetDatabase(DatabaseName, Path);
                List<string> table = new List<string>();
                DataTable dt = new Fang().GetAllTables(conn, DatabaseName);
                List<string> Insert = new List<string>();
                foreach(DataRow dr in dt.Rows)
                {
                    DataSet ds = GetTableDataSet(conn, dr["TABLE_NAME"].ToString(), DatabaseName);
                    string col = "";
                    foreach(DataRow ddrr in ds.Tables[0].Rows)
                    {
                        if (ddrr["主键"].ToString() == "√")
                        {
                            col = ddrr["字段名"].ToString(); ;
                        }
                    }
                    table.Add(SetTable(ds.Tables[0], Path));
                    DataSet ds1 = GetDataSet(conn, string.Format("select * from [{0}]", dr["TABLE_NAME"].ToString()), DatabaseName);
                    List<string> list = SetDataSet(ds1.Tables[0], dr["TABLE_NAME"].ToString(), Path,col);
                    string inse = "";
                    foreach(string str in list)
                    {
                        inse += str + "\n";
                    }
                    Insert.Add(inse +"go\n");
                }
                using(StreamWriter sw = new StreamWriter(Path,false, Encoding.GetEncoding("gb2312")))
                {
                    sw.Write(database);
                    foreach(string tab in table)
                    {
                        sw.WriteLine(tab);
                    }
                    foreach(string inse in Insert)
                    {
                        sw.WriteLine(inse);
                    }
                }

            }
            catch
            {

                throw;
            }

        }
        public string SetDatabase(string DatabaseName, string Path)
        {
            string sql = @"
use master
go
if exists (select * from sysdatabases where name ='" + DatabaseName + @"')
   drop database " + DatabaseName + @"
go
create database " + DatabaseName + "\ngo\n";
            using (StreamWriter sw = new StreamWriter(Path))
            {
                sw.WriteLine(sql);
            }
            return sql;
        }
        public string SetTable(DataTable dt, string Path)
        {
            string TableName = dt.Rows[0]["表名"].ToString();
            string TableLabel = dt.Rows[0]["表说明"].ToString();
            string Columns = "";
            int i = 1;
            foreach (DataRow dr in dt.Rows)
            {
                string column = "";
                if (i == dt.Rows.Count)
                {
                    column = string.Format("    [{0}] {1} {2} {3} {4} {5} {6}", dr["字段名"].ToString(), IsType(dr["类型"].ToString(), dr["长度"].ToString() == "-1" ? "Max" : dr["长度"].ToString())
                   , dr["标识"].ToString() == "√" ? " identity(1,1)" : "", dr["主键"].ToString() == "√" ? "primary key" : ""
                   , dr["默认值"].ToString() == "" ? "" : "default" + dr["默认值"].ToString() + "",
                   dr["允许空"].ToString() == "" ? "not null" : "", dr["字段说明"].ToString() == "" ? "" : "--" + dr["字段说明"].ToString());
                    Columns += column;
                    break;
                }
                column = string.Format("    [{0}] {1} {2} {3} {4} {5},{6}\n", dr["字段名"].ToString(), IsType(dr["类型"].ToString(), dr["长度"].ToString() == "-1" ? "Max" : dr["长度"].ToString())
                    , dr["标识"].ToString() == "√" ? " identity(1,1)" : "", dr["主键"].ToString() == "√" ? "primary key" : ""
                    , dr["默认值"].ToString() == "" ? "" : "default" + dr["默认值"].ToString() + "",
                    dr["允许空"].ToString() == "" ? "not null" : "", dr["字段说明"].ToString() == "" ? "" : "--" + dr["字段说明"].ToString());
                Columns += column;
                i++;
            }
            string sql = @"
if exists(select * from sysobjects where name = '" + TableName + @"')
    drop table [" + TableName + @"]
go
create table [" + TableName + "]\n(--" + TableLabel + @"
" + Columns + "\n)\ngo\n";
            using (StreamWriter sw = new StreamWriter(Path))
            {
                sw.WriteLine(sql);
            }
            return sql;
        }
        public string IsType(string type, string num)
        {
            switch (type)
            {
                case "int":
                    return "int";
                case "datetime":
                    return "datetime";
                case "bit":
                    return "bit";
                default:
                    return string.Format("{0}({1})", type, num);
            }
        }
        public List<string> SetDataSet(DataTable dt, string TableName, string Path,string col = "")
        {
            List<string> inserts = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                string columns = "";
                string values = "";
                foreach (DataColumn column in dt.Columns)
                {
                    if(col != column.ColumnName)
                    {
                        columns += "[" + column.ColumnName + "],";
                        values += string.Format("'{0}',", dr[column.ColumnName].ToString());
                    }

                }
                columns = columns.Substring(0, columns.Length - 1);
                values = values.Substring(0, values.Length - 1);
                inserts.Add(string.Format("insert into [{0}]({1}) values({2})", TableName, columns, values));
            }
            using (StreamWriter sw = new StreamWriter(Path))
            {
                foreach (string insert in inserts)
                {
                    sw.WriteLine(insert);
                }
            }
            return inserts;
        }
        public List<string> SetUpdate(DataTable dt, string TableName, string UpdateColumn, string UpdateValue, string Path)
        {
            List<string> inserts = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                string values = "";
                string update = string.Format("[{0}] = {1}", UpdateColumn, UpdateValue);
                foreach (DataColumn column in dt.Columns)
                {
                    if (column.ColumnName.ToLower() != UpdateColumn.ToLower())
                    {
                        values += string.Format("[{0}] = {1},", column.ColumnName, dr[column.ColumnName].ToString());
                    }
                    if (UpdateValue.Trim().Length == 0)
                    {
                        update = string.Format("[{0}] = {1}", UpdateColumn, dr[UpdateColumn].ToString());
                    }
                }
                values = values.Substring(0, values.Length - 1);
                inserts.Add(string.Format("update [{0}] set {1} where {2}", TableName, values, update));
            }
            using (StreamWriter sw = new StreamWriter(Path))
            {
                foreach (string insert in inserts)
                {
                    sw.WriteLine(insert);
                }
            }
            return inserts;
        }
        public DataSet GetDataSet(Conn con, string sql, string DatabaseName)
        {
            string ConnStr = "";
            if (con.Type == 0)
            {
                ConnStr = string.Format("serVer = {0}; database = {3};uid = {1};pwd = {2}", con.Fuwu, con.Id, con.Pwd, DatabaseName);
            }
            else
            {
                ConnStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", con.Fuwu, DatabaseName);
            }

            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnStr))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
        }
        public DataSet GetTableDataSet(Conn con, string tableName, string DatabaseName)
        {
            string ConnStr = "";
            if (con.Type == 0)
            {
                ConnStr = string.Format("serVer = {0}; database = {3};uid = {1};pwd = {2}", con.Fuwu, con.Id, con.Pwd, DatabaseName);
            }
            else
            {
                ConnStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", con.Fuwu, DatabaseName);
            }
            string sql = @"SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
 
 
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
where
d.name='" + tableName + @"'
order by
a.id,a.colorder
";
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnStr))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
        }
    }
}
